トランザクションを含むストアドプロシージャからエラーメッセージを取得する #SnowflakeDB

モダンデータスタック(MDS)
#Snowflake
安原朋紀
2024.10.31
 はじめにSnowflake では Snowflakeスクリプトを使用して、手続き型ロジックを使用しながら SQL でストアドプロシージャを記述することができます。ストアドプロシージャ内で明示的にトランザクションを開始することもできるのですが、この際、欲しいエラーメッセージをうまく取得できないことがありました。同様の質問がコミュニティにもあったのですが、この記事でもまとめみます。
https://snowflake.discourse.group/t/how-do-i-return-the-error-message-from-a-procedure-when-it-occurs-within-a-scoped-transaction/3076
 困っていたことサンプルとして、以下のようなストアドプロシージャを定義したとします。（サンプルなので例外はまとめています。）このストアドプロシージャでは、テーブルに対するインサートを行いますが、parent テーブルに対するインサート後、child テーブルへの処理前に、何らかのエラーが発生するような設定です。

※$$で囲まれた部分が Snowflakeスクリプトのブロックで、はじめの BEGIN はSnowflakeスクリプトのブロックを定義するものです（トランザクションの BEGIN とは異なります）。
CREATE OR REPLACE TABLE parent (ID INTEGER);
CREATE OR REPLACE TABLE child (ID INTEGER);

SELECT * FROM parent;
SELECT * FROM child;

CREATE OR REPLACE PROCEDURE insert_data()
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO parent VALUES (1);
    SELECT 1/0; --エラーを発生させる
    INSERT INTO child VALUES (1);
    COMMIT;
    RETURN 'complete';
EXCEPTION
  WHEN OTHER THEN
  RAISE;
END;
$$
;

CALL insert_data();
ストアドプロシージャを実行すると下図のようなメッセージが表示されます。
エラーの原因調査のためには、もう少し詳細なメッセージ（ここではゼロ除算によるエラー）が欲しかったのですが、エラーのためトランザクションがロールバックされたことしかわからず困っていました。
 結論結論としては、ストアドプロシージャの例外時の処理として、明示的にトランザクションをコミットまたはロールバックする処理が抜けていたことが原因でした。もう少し具体的には、それぞれ以下の仕様のためです。
明示的にトランザクションを開始した時、トランザクション内の DML ステートメントが失敗しても、トランザクション自体は明示的にコミットまたはロールバックされるまで、アクティブな状態のままである
https://docs.snowflake.com/ja/sql-reference/transactions#failed-statements-within-a-transaction
トランザクションがストアドプロシージャ内で開始され、ストアドプロシージャの終了時にまだアクティブである場合、エラーが発生し、トランザクションはロールバックされる
https://docs.snowflake.com/ja/sql-reference/transactions#stored-procedures-and-transactions
https://community.snowflake.com/s/article/Stored-procedure-execution-error-Scoped-transaction-started-in-stored-procedure-is-incomplete-and-it-was-rolled-back
サンプルクエリでは、明示的にトランザクションを開始し、途中例外が発生し例外処理を行います。すべての例外処理を実施（ここでは RAISE コマンドの実行）後も、トランザクションはアクティブなままであるため、ストアドプロシージャの終了時には「Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back」と表示されていました。

解決方法としては、例外処理内にROLLBACK（またはCOMMIT） を追加します。
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO parent VALUES (1);
    SELECT 1/0;
    INSERT INTO child VALUES (1);
    COMMIT;
    RETURN 'complete';
EXCEPTION
  WHEN OTHER THEN
  ROLLBACK; --明示的にトランザクションをコミットまたはロールバックする
  RAISE;
END;
この上でストアドプロシージャを再度実行する意図するエラーメッセージが返ってきました。
トランザクションはロールバックされているので、parent テーブルに値1は含まれません。
 暗黙的なトランザクションの場合AUTOCOMMIT が有効になっていれば、明示的な BEGIN TRANSACTIONとCOMMITまたはROLLBACK なしで、トランザクションを暗黙的に開始・終了できます。

AUTOCOMMIT が有効な状態で、以下のように明示的にトランザクションを開始・終了しないスクリプトとしてストアドプロシージャを定義します。ストアドプロシージャを実行すると、ゼロ除算によるエラーが発生し途中で処理はストップしますが、parent テーブルには、値1が追加されます。
BEGIN
    INSERT INTO parent VALUES (1);
    SELECT 1/0; --エラーを発生させる
    INSERT INTO child VALUES (1);
    RETURN 'complete';
EXCEPTION
  WHEN OTHER THEN
  RAISE;
END;
 さいごにストアドプロシージャ実行時のエラー「Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.」についてまとめてみました。

例外時の ROLLBACK 処理が抜けていたという単純なミスなのですが、改めて Snowflake のストアドプロシージャやトランザクション周りを勉強するよい機会になりました。こちらの内容が何かの参考になれば幸いです。